'''
2020@Wlfj.fun

'''
from openpyxl import Workbook, load_workbook

# 源数据路径
file_path = "软件学院19202成绩.xlsx"
# 输出文件名称
out_file_name = "out.xlsx"
# 需要导出的班级(只需要指定开头即可)
out_class = "1813"

# 只需要输入绩点不是1的科目
grade_points = {
    "工具课": 6,
}

except_course = [
    '线性代数C', '高等数学C （1）', '概率论与数理统计C', '计算机硬件基础',
    '软件工程导论', '面向对象程序设计', '大学英语A（3）', '大学英语A（1）', ]


class Student:
    def __init__(self, id, name):
        self.id = id
        self.name = name
        self.course = {}

    def get_points(self):
        # 参考资料：http://ss.nuc.edu.cn/info/1027/2859.htm

        sum_of_points = 0.0
        total_points = 0

        for course_name in self.course:
            if course_name in except_course:
                continue
            grade = self.course[course_name]
            if grade < 50:
                grade = 50
            sum_of_points += round(grade / 10 - 5, 2) * grade_points.get(course_name, 1)
            total_points += grade_points.get(course_name, 1)

        ans = sum_of_points / total_points

        return ans

# 以下部分不需要改动


wb = load_workbook(file_path)

ws = wb.active

database = {}
course_list = set()
# 现在我们要根据表格重新建立课程信息 因为其中包含课程学分 所以获取相对简单了
for row in ws.rows:
    if row[0].value == '学号':
        continue
    if not row[9].value.startswith(out_class):
        continue
    s_id = row[0].value
    s_name = row[1].value
    s_course_name = row[3].value
    if s_course_name in except_course:
        continue
    try:
        s_course_score = float(row[6].value)
    except ValueError:
        s_course_score = {
            '及格': 60,
            '优秀': 95,
            '良好': 85,
            '中': 75,
            '缺考': 0,
            '取消资格': 0,
            '不及格': 59,
            '缓考': 0,
            '违纪': 0,
            '作弊': 0,
        }[row[6].value]
    s_course_point = row[4].value

    course_list.add((s_course_name, s_course_point))

    if s_id not in database:
        database[s_id] = Student(id=s_id, name=s_name)

    stu = database[s_id]
    stu.course[s_course_name] = s_course_score

# 创建成绩字典
grade_points = {name: float(grade_p) for name, grade_p in course_list}
out_wb = Workbook()

out_ws = out_wb.active

out_ws.cell(row=1, column=1, value="学号")
out_ws.cell(row=1, column=2, value="姓名")

course_pos = {}

course_list = [name for name, g in course_list]

for i in range(len(course_list)):
    out_ws.cell(row=1, column=i+3, value=course_list[i])
    course_pos[course_list[i]] = i + 3

cur_row = 2
for stu_id in database:
    stu = database[stu_id]
    stu_name = stu.name
    out_ws.cell(row=cur_row, column=1, value=stu_id)
    out_ws.cell(row=cur_row, column=2, value=stu_name)
    for course_name in stu.course:
        out_ws.cell(row=cur_row, column=course_pos[course_name],
                    value=stu.course[course_name])
    out_ws.cell(row=cur_row, column=len(course_list)+3, value=stu.get_points())
    cur_row += 1

out_wb.save(out_file_name)
